from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, declarative_base
from sqlalchemy import Column, Integer, String, Boolean

from typing import Optional, List



mysql_url = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8".format(
    username="root", password="root", host="127.0.0.1", port=3306, database="demo01")
# SQLALCHEMY_DATABASE_URL = "sqlite:///.test.db"
engine = create_engine(
    mysql_url,
    # SQLALCHEMY_DATABASE_URL,
    # connect_args 参数只有sqlite才有
    # connect_args = {
    #     "check_same_thread": False
    # }
)

# 该类的每个实例都是一个数据库会话，该类本身还不是数据库会话，但是一旦我们创建了SessionLocal的实例，这个实例将是实际的数据库会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建数据库基类
Base = declarative_base()


# 创建两个数据库模型，继承自Base基类
# 一方
class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(20), unique=True, index=True)
    hashed_password = Column(String(20))
    is_active = Column(Boolean, default=True)
    items = relationship("Item", back_populates="owner")

# 多方
class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(10), index=True)
    description = Column(String(10), index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))
    owner = relationship("User", back_populates="items")


# 那么我们如何将该模型对应到数据库表呢? 很简单
# Base.metadata.create_all(bind=engine)

# ___________________________________________

from pydantic import BaseModel
from sqlalchemy.orm import Session
from fastapi import FastAPI, Depends, HTTPException


class UserBase(BaseModel):
    email: str


class UserCreate(UserBase):
    """请求验证模型"""
    password: str


class Users(UserBase):
    """响应模型"""
    id: int
    is_active: bool

    class Config:
        from_attributes = True





def get_user(db: Session, user_id: int):
    """通过id查询用户"""
    return db.query(User).filter(User.id == user_id).first()


def db_create_user(db: Session, user: UserCreate):
    """创建用户"""
    fake_hashed_password = user.password + "falsehash"
    print(">>>>>", user, fake_hashed_password)
    db_user = User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()  # 提交保存到数据库中
    db.refresh(db_user)
    return db_user


# 创建一个依赖
def get_db():
    """每一个请求处理完毕后会关闭当前连接，不同的请求使用不同的连接"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


from fastapi import FastAPI, Depends, HTTPException

app = FastAPI()


@app.post("/users", response_model=Users)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    """创建用户接口"""
    return db_create_user(db=db, user=user)


@app.get("/users/{user_id}", response_model=Users)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = get_user(db=db, user_id=user_id)
    if not db_user:
        raise HTTPException(status_code=404, detail="user not exists")
    return db_user


# -------------------------以下是多表操作
class BaseItem(BaseModel):
    title: str
    description: Optional[str] = None


class ItemModel(BaseItem):
    pass


class ItemOut(BaseItem):
    id: int
    owner_id: int

    class Config:
        from_attributes = True


@app.post("/items/{uid}", response_model=ItemOut)
def create_item_by_user(uid: int, item: ItemModel, db: Session = Depends(get_db)):
    # 先查询uid用户是否存在
    user = get_user(db, uid)
    if not user:
        raise HTTPException(status_code=404, detail="user not exists")
    init_item = Item(**item.model_dump(), owner_id=uid)
    db.add(init_item)
    db.commit()
    db.refresh(init_item)
    return init_item


# 分页获取所有的items
@app.get("/items", response_model=List[ItemOut])
def get_items(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
    return db.query(Item).offset(skip).limit(limit).all()


# 如何查询用户的items
@app.get("/items/{uid}", response_model=List[ItemOut])
def get_items_by_uid(uid: int,  skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.id == uid).first()
    if not user:
        raise HTTPException(status_code=200, detail="this user is not valid")
    return db.query(Item).filter(Item.owner == user).offset(skip).limit(limit).all()

# 如何查询拥有item的用户
@app.get("/user/{item_id}", response_model=Users)
def get_user_by_itemId(item_id: int, db: Session = Depends(get_db)):
    item = db.query(Item).filter(Item.id == item_id).first()
    if not item:
        raise HTTPException(status_code=200, detail="this item is not valid")
    return item.owner








if __name__ == '__main__':
    import uvicorn
    uvicorn.run("main:app", host="127.0.0.1", port=8080, reload=True)